package com.os.core.service.extend.impl;

import cn.hutool.core.collection.ListUtil;
import com.os.common.entity.file.FileAnalysis;
import com.os.common.exception.ErrorException;
import com.os.common.utils.FileUtil;
import com.os.core.service.extend.AnalysisService;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.nio.charset.StandardCharsets;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * 描述：文件分析实现类
 *
 * @author huxuehao
 **/
@Service
public class AnalysisServiceImpl implements AnalysisService {

    @Override
    public String analysisExcel(MultipartFile excelFile) throws Exception {
        // 文件名
        String originalFilename = excelFile.getOriginalFilename();
        assert originalFilename != null;
        if (originalFilename.endsWith(".xls")) {
            List<List<Object>> dataFromExcel = FileUtil.getDataFromExcel(true, excelFile.getInputStream());
            return genScript(originalFilename, dataFromExcel);
        }else if(originalFilename.endsWith(".xlsx")){
            List<List<Object>> dataFromExcel = FileUtil.getDataFromExcel(false, excelFile.getInputStream());
            return genScript(originalFilename, dataFromExcel);
        }else {
            throw new ErrorException("抱歉，文件类型不匹配，请选择excel文件");
        }
    }

    @Override
    public String analysisCsv(MultipartFile csvFile) throws Exception {
        // 文件名
        String originalFilename = csvFile.getOriginalFilename();
        assert originalFilename != null;
        if (!originalFilename.endsWith(".csv")) {
            throw new ErrorException("抱歉，文件类型不匹配，请选择csv文件");
        }
        // 获取csv中的数据
        List<List<Object>> dataFromCsv = FileUtil.getDataFromCsv(csvFile.getInputStream());
        return genScript(originalFilename, dataFromCsv);
    }

    @Override
    public String analysisTxt(MultipartFile txtFile) throws Exception {
        // 文件名
        String originalFilename = txtFile.getOriginalFilename();
        assert originalFilename != null;
        if (!originalFilename.endsWith(".txt")) {
            throw new ErrorException("抱歉，文件类型不匹配，请选择txt文件");
        }
        // 获取csv中的数据
        List<List<Object>> dataFromTxt = FileUtil.getDataFromCsv(txtFile.getInputStream());
        return genScript(originalFilename, dataFromTxt);
    }

    @Override
    public void analysisExcelAndDownLoad(MultipartFile excelFile, HttpServletResponse response) throws Exception {
        InputStream in = new ByteArrayInputStream(analysisExcel(excelFile).getBytes(StandardCharsets.UTF_8));
        setResponseOfDownload(response,in);
    }

    @Override
    public void analysisCsvAndDownLoad(MultipartFile csvFile, HttpServletResponse response) throws Exception {
        InputStream in = new ByteArrayInputStream(analysisCsv(csvFile).getBytes(StandardCharsets.UTF_8));
        setResponseOfDownload(response,in);
    }

    @Override
    public void analysisTxtAndDownLoad(MultipartFile txtFile, HttpServletResponse response) throws Exception {
        InputStream in = new ByteArrayInputStream(analysisTxt(txtFile).getBytes(StandardCharsets.UTF_8));
        setResponseOfDownload(response,in);
    }

    /* 设置下载文件的response */
    private void setResponseOfDownload(HttpServletResponse response, InputStream in ) throws Exception {
        byte[] buffer = new byte[1024];
        try (OutputStream os = response.getOutputStream();
             BufferedInputStream bis = new BufferedInputStream(in))
        {
            FileUtil.setDownloadHeader(response);
            int len = bis.read(buffer);
            while(len != -1){
                os.write(buffer,0,len);
                len = bis.read(buffer);
            }
        }
    }

    /* 生成脚本 */
    private String genScript(String originalFilename, List<List<Object>> dataFrom) {
        List<Object> defaultTableType;
        List<Object> defaultFieldName;
        List<String> scriptList = new LinkedList<>();
        if (dataFrom.size()>1){
            // 获取默认字段类型
            defaultTableType = dataFrom.get(dataFrom.size() - 1);
            defaultFieldName = dataFrom.get(0);
        } else {
            throw new ErrorException("excel中除表头外数据为空");
        }
        // 移除表头
        dataFrom.remove(0);
        // 移除表尾
        dataFrom.remove(dataFrom.size() - 1);
        FileAnalysis excelDescribe = new FileAnalysis(
                removeFilenameSuffixes(originalFilename),
                defaultFieldName,
                defaultTableType,
                dataFrom,
                Collections.singletonList("id"));
        // 生成创建语句
        scriptList.add(genDDL(excelDescribe));
        scriptList.addAll(createInsertScripts(excelDescribe));
        return String.join("\n", scriptList);
    }

    /* 去除文件名后缀 */
    private String removeFilenameSuffixes(String fileName) {
        List<String> list = ListUtil.toList(fileName.split("\\."));
        if (list.size()==2) {
            return list.get(0);
        } else if (list.size() >2){
            StringBuilder tableName = new StringBuilder();
            for (int i = 0; i < list.size() - 2; i++) {
                tableName.append(list.get(i)).append(".");
                if (i+1 == list.size() - 2) {
                    tableName.append(list.get(i));
                }
            }
            return tableName.toString();
        }else {
            return fileName;
        }
    }

    /* 生成创建语句 */
    private String genDDL(FileAnalysis fileDescribe) {
        String defaultTableName = fileDescribe.getDefaultTableName();
        List<Object> defaultFieldType = fileDescribe.getDefaultFieldType();
        List<Object> defaultFieldName = fileDescribe.getDefaultFieldName();
        List<Object> primaryKey = fileDescribe.getPrimaryKey();
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE TABLE `").append(defaultTableName).append("`(\n");
        for (int i = 0; i < defaultFieldName.size(); i++) {
            if ("varchar".equalsIgnoreCase(defaultFieldType.get(i).toString())){
                sql.append("`").append(defaultFieldName.get(i).toString()).append("` ").append(defaultFieldType.get(i).toString()).append("(255)");
            } else {
                sql.append("`").append(defaultFieldName.get(i).toString()).append("` ").append(defaultFieldType.get(i).toString());
            }
            sql.append(",\n");
        }
        if (primaryKey != null && primaryKey.size() > 0) {
            sql.append("PRIMARY KEY (");
            for (Object obj : primaryKey) {
                sql.append("`").append(obj.toString()).append("`");
                sql.append(",");
            }
            // 删除最后的“,\n”
            sql.delete(sql.length()-1,sql.length());
            sql.append("),");
        }
        // 删除最后的“,或者\n”
        sql.delete(sql.length()-1,sql.length());
        sql.append("\n);");
        return sql.toString();
    }

    /* 生成插入语句 */
    private List<String> createInsertScripts(FileAnalysis fileDescribe) {
        String defaultTableName = fileDescribe.getDefaultTableName();
        List<Object> defaultFieldName = fileDescribe.getDefaultFieldName();
        List<List<Object>> dataList = fileDescribe.getDataList();
        List<String> list = new LinkedList<>();

        for (List<Object> columnValueUnit : dataList) {
            StringBuilder sql = new StringBuilder();
            sql.append("INSERT INTO `").append(defaultTableName).append("`(");
            String columnUnit = defaultFieldName
                    .stream()
                    .map(item -> "`" + item.toString() + "`")
                    .collect(Collectors.joining(","));
            sql.append(columnUnit).append(") VALUES(");
            String valueUnit = columnValueUnit
                    .stream()
                    .map(item -> "'" + item.toString() + "'")
                    .collect(Collectors.joining(","));
            sql.append(valueUnit).append(");");
            list.add(sql.toString());
        }
        return list;
    }
}
